package cn.conac.rc.monitor.repository;

import cn.conac.rc.monitor.entity.VLocalMonitorDay;
import cn.conac.rc.monitor.entity.VLocalMonitorDayStage1;

import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import cn.conac.rc.framework.repository.GenericDao;

import java.util.List;

import static cn.conac.rc.monitor.repository.VLocalMonitorDayStage1Repository.groupby_BM;

@Repository
public interface VLocalMonitorDayStage1Repository extends GenericDao<VLocalMonitorDayStage1, String>
{
    public static final String column_comm =
            " sum(t.sq_count) as sq_count,"
                    + "sum(t.sl_count) as sl_count,"
                    + "sum(t.bsl_count) as bsl_count,"
                    + "sum(t.bj_count) as bj_count,"
                    + "sum(t.xkbj_count) as xkbj_count,"
                    + "sum(t.byxkbj_count) as byxkbj_count,"
                    + "sum(t.zbbj_count) as zbbj_count,"
                    + "sum(t.zzbj_count) as zzbj_count,"
                    + "sum(t.grbj_count) as grbj_count,"
                    + "sum(t.frbj_count) as frbj_count,"
                    + "sum(t.ycxbz_count) as ycxbz_count,"
                    + "sum(t.bjcs_count) as bjcs_count,"
                    + "sum(t.wbjcs_count) as wbjcs_count,"
                    + "sum(t.cksq_count) as cksq_count,"
                    + "sum(t.wssq_count) as wssq_count,"
                    + "sum(t.tbcxsq_count) as tbcxsq_count, "
                    + "sum(t.tbcxcs_count) as tbcxcs_count, "
                    + "sum(t.bzs_count) as bzs_count, "
                    + "sum(t.tbcxcscs_count) as tbcxcscs_count, "
                    + "sum(t.bjysts_count) as bjysts_count, "
                    + "sum(t.bzts_count) as bzts_count, "
                    + "sum(t.tbcxts_count) as tbcxts_count, ";
    public static final String select_group_M = "SELECT substr(tj_date, 0, 7) as tj_date,";
    public static final String select_group_Q =
            "SELECT substr(t.tj_date, 0, 4) tj_date, TO_CHAR(TO_DATE(t.tj_date, 'yyyy-MM-dd'), 'Q') blbm,";
    public static final String select_group_Y = "SELECT substr(tj_date, 0, 4) tj_date,";
    public static final String select_group_BM = "SELECT t.blbm,";
    public static final String select_group_BM_SX = "SELECT t.blbm,t.dfsxbm,t.sxmc,";
    public static final String column_M = " '#' as dfsxbm,'#' as sxmc,'#' as blbm,'#' as xzqhbm ,sys_guid() as id";
    public static final String column_q = " '#' as dfsxbm,'#' as sxmc,'#' as xzqhbm,sys_guid() as id";
    public static final String column_BM_SX = " '#' as tj_date,'#' as xzqhbm ,sys_guid() as id";
    public static final String column_BM = " '#' as dfsxbm,'#' as sxmc,'#' as tj_date,'#' as xzqhbm ,sys_guid() as id";
    public static final String FROM_STAGE1 = " FROM V_LOCAL_MONITOR_DAY_STAGE1 T ";
    public static final String FROM_STAGE2 = " FROM V_LOCAL_MONITOR_DAY_STAGE2 T ";
    public static final String FROM_STAGE3 = " FROM V_LOCAL_MONITOR_DAY_STAGE3 T ";
    public static final String where_dptment_arCode_stDate_edDate =
        "where (t.blbm =:department ) and (t.xzqhbm=:areaCode or t.xzqhbm is null) and t.tj_date BETWEEN :startDate AND :endDate ";
    public static final String where_dfsxbm_arCode_stDate_edDate =
            "where t.blbm is not null and (t.dfsxbm =:dfsxbm or t.sxmc =:dfsxbm) and (t.xzqhbm=:areaCode or t.xzqhbm is null) and t.tj_date BETWEEN :startDate AND :endDate ";
    public static final String where_dptment_dfsxbm_arCode_stDate_edDate =
            "where t.blbm =:department  and (t.dfsxbm =:dfsxbm or t.sxmc =:dfsxbm) and (t.xzqhbm=:areaCode or t.xzqhbm is null) and t.tj_date BETWEEN :startDate AND :endDate ";
    public static final String where_arCode_stDate_edDate =
            " where t.blbm is not null and  (t.xzqhbm=:areaCode or t.xzqhbm is null) and t.tj_date BETWEEN :startDate AND :endDate ";
    public static final String groupby_M =
            " GROUP BY substr(tj_date, 0, 7)  ORDER BY substr(tj_date, 0, 7) ASC NULLS LAST";
    public static final String groupby_q =
            " GROUP BY TO_CHAR(TO_DATE(t.tj_date, 'yyyy-MM-dd'), 'Q'),substr(t.tj_date, 0, 4)  ORDER BY substr(t.tj_date, 0, 4),TO_CHAR(TO_DATE(t.tj_date, 'yyyy-MM-dd'), 'Q') ASC NULLS LAST ";
    public static final String groupby_Y =
            " GROUP BY substr(tj_date, 0, 4) ORDER BY substr(tj_date, 0, 4) ASC NULLS LAST  ";
    public static final String groupby_BM = " GROUP BY t.blbm ORDER BY t.blbm ASC NULLS LAST   ";
    public static final String groupby_BM_SX = " GROUP BY t.blbm,t.dfsxbm,t.sxmc ORDER BY t.blbm ASC NULLS LAST   ";

    @Query(value = select_group_M + column_comm + column_M + FROM_STAGE1 + where_dfsxbm_arCode_stDate_edDate
            + groupby_M, nativeQuery = true)
    List<VLocalMonitorDayStage1> getF3dCountsByMonthStage1(@Param("dfsxbm") String dfsxbm,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);
    @Query(value = select_group_M + column_comm + column_M + FROM_STAGE2 + where_dfsxbm_arCode_stDate_edDate
            + groupby_M, nativeQuery = true)
    List<VLocalMonitorDayStage1> getF3dCountsByMonthStage2(@Param("dfsxbm") String dfsxbm,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);
    @Query(value = select_group_M + column_comm + column_M + FROM_STAGE3 + where_dfsxbm_arCode_stDate_edDate
            + groupby_M, nativeQuery = true)
    List<VLocalMonitorDayStage1> getF3dCountsByMonthStage3(@Param("dfsxbm") String dfsxbm,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);
    @Query(value = select_group_M + column_comm + column_M + FROM_STAGE1 + where_dptment_arCode_stDate_edDate
            + groupby_M, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByMonthStage1(@Param("department") String department,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);
    @Query(value = select_group_M + column_comm + column_M + FROM_STAGE1 + where_dptment_dfsxbm_arCode_stDate_edDate
            + groupby_M, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByMonthStage1(@Param("department") String department,
            @Param("dfsxbm") String dfsxbm,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);
    @Query(value = select_group_M + column_comm + column_M + FROM_STAGE2 + where_dptment_dfsxbm_arCode_stDate_edDate
            + groupby_M, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByMonthStage2(@Param("department") String department,
            @Param("dfsxbm") String dfsxbm,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);
    @Query(value = select_group_M + column_comm + column_M + FROM_STAGE3 + where_dptment_dfsxbm_arCode_stDate_edDate
            + groupby_M, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByMonthStage3(@Param("department") String department,
            @Param("dfsxbm") String dfsxbm,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);
    @Query(value = select_group_M + column_comm + column_M + FROM_STAGE1 + where_arCode_stDate_edDate
            + groupby_M, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByMonthStage1(@Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_Q + column_comm + column_q + FROM_STAGE1 + where_dptment_arCode_stDate_edDate
            + groupby_q, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByQuarterStage1(@Param("department") String department,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);
    @Query(value = select_group_Q + column_comm + column_q + FROM_STAGE1 + where_dptment_dfsxbm_arCode_stDate_edDate
            + groupby_q, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByQuarterStage1(@Param("department") String department,
            @Param("dfsxbm") String dfsxbm,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);
    @Query(value = select_group_Q + column_comm + column_q + FROM_STAGE2 + where_dptment_dfsxbm_arCode_stDate_edDate
            + groupby_q, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByQuarterStage2(@Param("department") String department,
            @Param("dfsxbm") String dfsxbm,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);
    @Query(value = select_group_Q + column_comm + column_q + FROM_STAGE1 + where_arCode_stDate_edDate
            + groupby_q, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByQuarterStage1(@Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_Y + column_comm + column_M + FROM_STAGE1 + where_dptment_arCode_stDate_edDate
            + groupby_Y, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByYearStage1(@Param("department") String department,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);
    @Query(value = select_group_Y + column_comm + column_M + FROM_STAGE1 + where_dptment_dfsxbm_arCode_stDate_edDate
            + groupby_Y, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByYearStage1(@Param("department") String department,
            @Param("dfsxbm") String dfsxbm,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);
    @Query(value = select_group_Y + column_comm + column_M + FROM_STAGE2 + where_dptment_dfsxbm_arCode_stDate_edDate
            + groupby_Y, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByYearStage2(@Param("department") String department,
            @Param("dfsxbm") String dfsxbm,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);
    @Query(value = select_group_Y + column_comm + column_M + FROM_STAGE3 + where_dptment_dfsxbm_arCode_stDate_edDate
            + groupby_Y, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByYearStage3(@Param("department") String department,
            @Param("dfsxbm") String dfsxbm,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);
    @Query(value = select_group_Y + column_comm + column_M + FROM_STAGE1 + where_arCode_stDate_edDate
            + groupby_Y, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByYearStage1(@Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_M + column_comm + column_M + FROM_STAGE2 + where_dptment_arCode_stDate_edDate
            + groupby_M, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByMonthStage2(@Param("department") String department,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_M + column_comm + column_M + FROM_STAGE2 + where_arCode_stDate_edDate
            + groupby_M, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByMonthStage2(@Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_Q + column_comm + column_q + FROM_STAGE2 + where_dptment_arCode_stDate_edDate
            + groupby_q, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByQuarterStage2(@Param("department") String department,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_Q + column_comm + column_q + FROM_STAGE2 + where_arCode_stDate_edDate
            + groupby_q, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByQuarterStage2(@Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_Y + column_comm + column_M + FROM_STAGE2 + where_dptment_arCode_stDate_edDate
            + groupby_Y, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByYearStage2(@Param("department") String department,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_Y + column_comm + column_M + FROM_STAGE2 + where_arCode_stDate_edDate
            + groupby_Y, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByYearStage2(@Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_M + column_comm + column_M + FROM_STAGE3 + where_dptment_arCode_stDate_edDate
            + groupby_M, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByMonthStage3(@Param("department") String department,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_M + column_comm + column_M + FROM_STAGE3 + where_arCode_stDate_edDate
            + groupby_M, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByMonthStage3(@Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);
    @Query(value = select_group_Q + column_comm + column_q + FROM_STAGE3 + where_dptment_dfsxbm_arCode_stDate_edDate
            + groupby_q, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByQuarterStage3(@Param("department") String department,
            @Param("dfsxbm") String dfsxbm,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);
    @Query(value = select_group_Q + column_comm + column_q + FROM_STAGE3 + where_dptment_arCode_stDate_edDate
            + groupby_q, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByQuarterStage3(@Param("department") String department,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_Q + column_comm + column_q + FROM_STAGE3 + where_arCode_stDate_edDate
            + groupby_q, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByQuarterStage3(@Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_Y + column_comm + column_M + FROM_STAGE3 + where_dptment_arCode_stDate_edDate
            + groupby_Y, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByYearStage3(@Param("department") String department,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_Y + column_comm + column_M + FROM_STAGE3 + where_arCode_stDate_edDate
            + groupby_Y, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByYearStage3(@Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_BM + column_comm + column_BM + FROM_STAGE3 + where_dptment_arCode_stDate_edDate
            + groupby_BM, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByBlbm(@Param("department") String department,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_BM + column_comm + column_BM + FROM_STAGE3 + where_arCode_stDate_edDate
            + groupby_BM, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByBlbm(@Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);
    @Query(value = select_group_BM_SX + column_comm + column_BM_SX + FROM_STAGE3 + where_dptment_arCode_stDate_edDate
            + groupby_BM_SX, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsBySx(@Param("department") String department,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);
    @Query(value = select_group_BM_SX + column_comm + column_BM_SX + FROM_STAGE1 + where_arCode_stDate_edDate
            + groupby_BM_SX, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByBlbmSx1(@Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_BM_SX + column_comm + column_BM_SX + FROM_STAGE1 + where_dptment_arCode_stDate_edDate
            + groupby_BM_SX, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByBlbmSx1(@Param("areaCode") String department,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_BM_SX + column_comm + column_BM_SX + FROM_STAGE2 + where_arCode_stDate_edDate
            + groupby_BM_SX, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByBlbmSx2(@Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_BM_SX + column_comm + column_BM_SX + FROM_STAGE2 + where_dptment_arCode_stDate_edDate
            + groupby_BM_SX, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByBlbmSx2(@Param("areaCode") String department,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_BM_SX + column_comm + column_BM_SX + FROM_STAGE3 + where_arCode_stDate_edDate
            + groupby_BM_SX, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByBlbmSx3(@Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_BM_SX + column_comm + column_BM_SX + FROM_STAGE3 + where_dptment_arCode_stDate_edDate
            + groupby_BM_SX, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByBlbmSx3(@Param("areaCode") String department,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);


    @Query(value = select_group_BM + column_comm + column_BM + FROM_STAGE1 + where_arCode_stDate_edDate
            + groupby_BM, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByBlbm1(@Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_BM + column_comm + column_BM + FROM_STAGE1 + where_dptment_arCode_stDate_edDate
            + groupby_BM, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByBlbm1(@Param("areaCode") String department,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_BM + column_comm + column_BM + FROM_STAGE2 + where_arCode_stDate_edDate
            + groupby_BM, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByBlbm2(@Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_BM + column_comm + column_BM + FROM_STAGE2 + where_dptment_arCode_stDate_edDate
            + groupby_BM, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByBlbm2(@Param("areaCode") String department,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_BM + column_comm + column_BM + FROM_STAGE3 + where_arCode_stDate_edDate
            + groupby_BM, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByBlbm3(@Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);

    @Query(value = select_group_BM + column_comm + column_BM + FROM_STAGE3 + where_dptment_arCode_stDate_edDate
            + groupby_BM, nativeQuery = true)
    List<VLocalMonitorDayStage1> getCountsByBlbm3(@Param("areaCode") String department,
            @Param("areaCode") String areaCode,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate);
}
